<?php
/**
 * This file contains a simple wrapper class
 * for MySQL.
 * 
 * @copyright 2008 Daniel Fone
 */

class MySQL {
	public static $link;
	public static $host = '';
	public static $user = '';
	public static $pass = '';
	public static $error = '';
	public static $lastQuery = '';
	
	// We don't need to show these
	public static $systemDBs = array(
		'mysql',
		'information_schema'
	);
	
	/**
	 * Connect to a MySQL server
	 * You'll need to close before you can reconnect
	 */
	static function connect($host, $user, $pass){
		MySQL::$host = $host;
		MySQL::$user = $user;
		MySQL::$pass = $pass;
		if ( ! (MySQL::$link = @mysql_pconnect($host, $user, $pass))) return false;
		return true;
	}
	
	/**
	 * List all of the databases on the server
	 * @return 
	 */
	static function dbList(){
		$dbs = array();
		foreach ( MySQL::query('SHOW DATABASES') as $record ){
			$dbs[] = $record['Database'];
		}
		return $dbs;		
	}
	
	/**
	 * Select the database to execute queries on
	 */
	static function selectDatabase($database){
		return MySQL::query("USE `$database`");
	}
	
	/**
	 * Perform a query and return an associative array
	 * of results
	 */
	static function query($query){
		Utility::debug($query);
		$records = array();
		// Execute query
		$result = mysql_query($query);
		MySQL::$lastQuery = $query;
		// Error checking
		if ( ! $result ){
			MySQL::$error = mysql_error();
			return false;	
		} elseif ( $result === true ){
			return true;
		}
		
		// Iterate through results
		while ($record = mysql_fetch_array($result)) {
			$records[] = $record;
		}
		// Free resultset
		mysql_free_result($result);
		//print "==> $query"; HTML::dump($records); print "==> END<br>";
		return $records;
	}
	
	/**
	 * Close the current connection so we can
	 * connect to somewhere different
	 */
	static function close(){
		mysql_close(MySQL::$link);
	}
		
	static function recordCount($dbname, $tablename){
		$records = MySQL::query("SELECT COUNT(*) FROM `$tablename`.`$dbname`");
		return $records[0][0];
	}
	
	static function dbCreate($dbname){
		return MySQL::query("CREATE DATABASE `$dbname`");
	}
	
	static function dbDelete($dbname){
		return MySQL::query("DROP DATABASE `$dbname`");
	}
	
	static function tableCreate($tablename){
		return MySQL::query("CREATE TABLE `$tablename` (`id` INT(11) AUTO_INCREMENT PRIMARY KEY)");
	}
	
	/**
	 * Return an array of table names contained
	 * in the specified database
	 */
	static function tableList(){
		$tables = array();
		foreach ( MySQL::query("SHOW TABLES") as $record ){
			$tables[] = $record[0];
		}
		return $tables;
	}
	
	static function tableRename($tablename, $newtablename){
		return MySQL::query("RENAME TABLE `$tablename` TO `$newtablename`");
	}
	
	static function tableDescribe($tablename){
		return MySQL::query("DESCRIBE `$tablename`");
	}
	
	static function tableDelete($tablename){
		return MySQL::query("DROP TABLE `$tablename`");
	}
	
	static function tableAddField($tablename, $colname, $coltype){
		return MySQL::query("ALTER TABLE `$tablename` ADD `$colname` $coltype");
	}
	
	static function tableAlterField($tablename, $oldcolname, $colname, $coltype, $after=''){
		if ( $after != '' and $after == 'first' ) $after = 'FIRST';
		elseif ( $after != '' ) $after = "AFTER `$after`";
		return MySQL::query("ALTER TABLE `$tablename` CHANGE `$oldcolname` `$colname` $coltype $after");
	}
	
	static function tableDeleteField($tablename, $colname){
		return MySQL::query("ALTER TABLE `$tablename` DROP `$colname`");
	}
	
	static function tableFields($tablename){
		return MySQL::tableDescribe($tablename);
	}
	
	static function tableRecordCount($tablename){
		$rows = MySQL::query("SELECT COUNT(*) FROM `$tablename`");
		return $rows[0][0];
	}
	
	static function tableRecords($tablename, $params=array()){
		extract($params);
		$fromAlias		= isset($fromAlias) ? $fromAlias : $tablename;
		$join			= isset($join) ? $join : '';
		$where			= isset($where) ? $where : '1';
		$extraFields	= isset($extraFields) && is_array($extraFields) ? $extraFields : array();
		$fields = array();
		$fields[] = "`$fromAlias`.*";
		foreach ( $extraFields as $field ){
			$fields[] = "$field[sql] AS '$field[name]'";
		}
		$fieldlist = join(', ', $fields);
		$query = "SELECT $fieldlist\nFROM `$tablename` AS `$fromAlias`\n$join\nWHERE $where\nGROUP BY `$fromAlias`.`id`";
		return MySQL::query($query);
	}
	
	static function tableSelectColumn($tablename, $column){
		$col = array();
		$records = MySQL::query("SELECT `$column` FROM `$tablename`");
		if ($records){
			foreach ( $records as $record ){
				$col[] = $record["$column"];
			}	
		}
		return $col;
	}
	
	static function tableCreateRecord($tablename, $fields){
		$myFields = $myVals = array();
		foreach ( $fields as $field => $val ){
			$myFields[] = "`$field`";
			$myVals[]	= "'".mysql_real_escape_string("$val")."'";
			
		}
		$field_list = join(', ', $myFields);
		$value_list = join(', ', $myVals);
		$ret = MySQL::query("INSERT INTO `$tablename` ($field_list) VALUES ($value_list)");
		if ( ! $ret ) return false;
		return mysql_insert_id();
	}
	
	static function tableUpdateRecord($tablename, $recordID, $fields){
		$mySets = array();
		foreach ( $fields as $field => $val ){			
			$mySets[] = "`$field` = '$val'";
		}
		$set_list = join(', ', $mySets);
		return MySQL::query("UPDATE `$tablename` SET $set_list WHERE `id`='$recordID'");
	}
	
	static function tableDeleteRecord($tablename, $recordID){
		return MySQL::query("DELETE FROM `$tablename` WHERE `id`='$recordID'");
	}
}